{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "0yZDpzmZglow"
   },
   "source": [
    "# Baseline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "m9qbOs6F20ey"
   },
   "source": [
    "### install library"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 122
    },
    "colab_type": "code",
    "collapsed": false,
    "executionInfo": {
     "elapsed": 5547,
     "status": "ok",
     "timestamp": 1546051723472,
     "user": {
      "displayName": "Cheng Gavin",
      "photoUrl": "",
      "userId": "16557339152332771497"
     },
     "user_tz": -480
    },
    "id": "CedseObJhaiv",
    "outputId": "404cd2f3-1498-413d-928a-71e206be3d6d"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting pymysql\n",
      "\u001b[?25l  Downloading https://files.pythonhosted.org/packages/ed/39/15045ae46f2a123019aa968dfcba0396c161c20f855f11dea6796bcaae95/PyMySQL-0.9.3-py2.py3-none-any.whl (47kB)\n",
      "\u001b[K    100% |████████████████████████████████| 51kB 1.8MB/s \n",
      "\u001b[?25hInstalling collected packages: pymysql\n",
      "Successfully installed pymysql-0.9.3\n"
     ]
    }
   ],
   "source": [
    "!pip install pymysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 122
    },
    "colab_type": "code",
    "collapsed": false,
    "executionInfo": {
     "elapsed": 31931,
     "status": "ok",
     "timestamp": 1546051707078,
     "user": {
      "displayName": "Cheng Gavin",
      "photoUrl": "",
      "userId": "16557339152332771497"
     },
     "user_tz": -480
    },
    "id": "mrXGgO-U3E2R",
    "outputId": "6c91ceeb-3401-46bb-c181-db275b5da455"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Go to this URL in a browser: https://accounts.google.com/o/oauth2/auth?client_id=947318989803-6bn6qk8qdgf4n4g3pfee6491hc0brc4i.apps.googleusercontent.com&redirect_uri=urn%3Aietf%3Awg%3Aoauth%3A2.0%3Aoob&scope=email%20https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fdocs.test%20https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fdrive%20https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fdrive.photos.readonly%20https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fpeopleapi.readonly&response_type=code\n",
      "\n",
      "Enter your authorization code:\n",
      "··········\n",
      "Mounted at /content/drive\n"
     ]
    }
   ],
   "source": [
    "from google.colab import drive\n",
    "drive.mount('/content/drive')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "x_Uk0PHO2cs9"
   },
   "source": [
    "## Functional code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "UVbPjjhWhR3y"
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import pymysql\n",
    "import pymysql.cursors\n",
    "from functools import reduce\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import uuid\n",
    "import datetime\n",
    "#from pyfm import pylibfm\n",
    "from sklearn.feature_extraction import DictVectorizer\n",
    "from sklearn.metrics.pairwise import pairwise_distances\n",
    "np.set_printoptions(precision=3)\n",
    "np.set_printoptions(suppress=True)\n",
    "from sklearn.datasets import dump_svmlight_file\n",
    "from sklearn.preprocessing import OneHotEncoder\n",
    "import pickle as pkl\n",
    "from sklearn.metrics import roc_auc_score, mean_squared_error\n",
    "from sklearn.datasets import load_svmlight_file\n",
    "from sklearn.linear_model import LogisticRegression"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "0txVlPpohhRN"
   },
   "outputs": [],
   "source": [
    "def get_connection():\n",
    "    return pymysql.connect(host='rm-2zeqqm6994abi7b6dqo.mysql.rds.aliyuncs.com',\n",
    "                               user='noone',\n",
    "                               password='Huawei12#$',\n",
    "                               db='recsys',\n",
    "                               port=3306,\n",
    "                               charset ='utf8',\n",
    "                               use_unicode=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "X5TyWGLchuGU"
   },
   "outputs": [],
   "source": [
    "def get_comment_data():\n",
    "  df_comment_new_data = pd.read_sql_query(\"select * from comment_new where newdata = 1 \", get_connection())\n",
    "  df_comment_new_data_ldim = df_comment_new_data.loc[:,['ID','MOVIEID','USERID']]\n",
    "  return df_comment_new_data_ldim"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "NHYzpf5ujxxA"
   },
   "outputs": [],
   "source": [
    "def get_ibmovie_by_movieid(movieid, connection):\n",
    "  sql = 'select DISTINCT recmovieid from ibmovie where movieid = \\'%s\\'' % movieid\n",
    "  try:\n",
    "      with connection.cursor() as cursor:\n",
    "          cout=cursor.execute(sql)\n",
    "      return cursor.fetchall()\n",
    "  except Exception as e:\n",
    "      print(e)\n",
    "      connection.close()\n",
    "  return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "p7W895yclOF9"
   },
   "outputs": [],
   "source": [
    "def insert_or_update_recmovie(movieid, userid, srcmovieid, connection):\n",
    "  _id = uuid.uuid4()\n",
    "  time_now = datetime.datetime.now()\n",
    "  q_sql = 'select id from recmovie where userid=\\'%s\\' and movieid=\\'%s\\'' % (userid, movieid)\n",
    "  i_sql = 'insert into recmovie (id, userid, movieid, rectime, srcmovieid) values (\\'%s\\', \\'%s\\', \\'%s\\', \\'%s\\', \\'%s\\')' % (_id, userid, movieid, time_now, srcmovieid)\n",
    "  exist_list = None\n",
    "  try:\n",
    "      with connection.cursor() as cursor:\n",
    "        #print(q_sql)\n",
    "        cout=cursor.execute(q_sql)\n",
    "        exist_list = cursor.fetchall()\n",
    "      if len(exist_list) > 0:\n",
    "        with connection.cursor() as cursor:\n",
    "          for item in exist_list:\n",
    "              u_sql = 'update recmovie set rectime=\\'%s\\' where id=\\'%s\\'' % (time_now, item[0])\n",
    "              cursor.execute(u_sql)\n",
    "      else:\n",
    "        with connection.cursor() as cursor:\n",
    "              cursor.execute(i_sql)\n",
    "  except Exception as e:\n",
    "      print(e)\n",
    "      connection.close()\n",
    "  return None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "bOVusyPJsMWT"
   },
   "source": [
    "* test code\n",
    "    \n",
    "    insert_or_update_recmovie('10430817','cf2349f9c01f9a5cd4050aebd30ab74f',conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "vQ37HM0p24Sj"
   },
   "outputs": [],
   "source": [
    "def update_comment_new_data_flag(rid, connection):\n",
    "  sql = 'update comment_new set newdata = 0 where id = \\'%s\\'' % rid\n",
    "  try:\n",
    "      with connection.cursor() as cursor:\n",
    "          cout=cursor.execute(sql)\n",
    "  except Exception as e:\n",
    "      print(e)\n",
    "      connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "bdENKeeliCir"
   },
   "outputs": [],
   "source": [
    "def func_main():\n",
    "  df_comment_new_data_ldim = get_comment_data()\n",
    "  conn = get_connection()\n",
    "  for i in df_comment_new_data_ldim.index:\n",
    "    print(df_comment_new_data_ldim.iloc[i]['MOVIEID'], df_comment_new_data_ldim.iloc[i]['USERID'])\n",
    "    ibmovie_list = get_ibmovie_by_movieid(df_comment_new_data_ldim.iloc[i]['MOVIEID'], get_connection())\n",
    "    for j in ibmovie_list:\n",
    "      insert_or_update_recmovie(j[0],'cf2349f9c01f9a5cd4050aebd30ab74f', df_comment_new_data_ldim.iloc[i]['MOVIEID'], conn)\n",
    "    update_comment_new_data_flag(df_comment_new_data_ldim.iloc[i]['ID'], conn)\n",
    "    conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "7a23-I7si4fW"
   },
   "outputs": [],
   "source": [
    "#func_main()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "collapsed": true,
    "id": "7mQQnmQSs8xN"
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "7_recbaseline.ipynb",
   "provenance": [],
   "version": "0.3.2"
  },
  "kernelspec": {
   "display_name": "Python [default]",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
